﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class ThanhVien
    {
        //private const String SConn = "Data Source=DUCLINH-PC;Initial Catalog=HOUSMS;Persist Security Info=True;User ID=sa;Password=minhduc";
        private static string SConn = ConfigurationManager.ConnectionStrings["DucLM"].ConnectionString;
        #region "Kiểm tra xem thành viên đã tồn tại hay chưa?"
        public static DataTable Find(String username, String password)
        {
            var conn = new SqlConnection(SConn);
            var da = new SqlDataAdapter("Login_Check", conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("@MemberName", username);
            da.SelectCommand.Parameters.AddWithValue("@Password", password);
            var ds = new DataSet();
            da.Fill(ds, "LoginTable");
            DataTable dt = ds.Tables["LoginTable"];
            return dt;
        }
        #endregion

        #region "Thêm mới thành viên"
        public int Add(String tenThanhvien, String matKhau, int maVaiTro, int maCauHoi, String ndTraLoi)
        {
            var conn = new SqlConnection(SConn);
            conn.Open();
            var cmd = new SqlCommand("InsertMember", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                cmd.Parameters.AddWithValue("@TenThanhVien", tenThanhvien);
                cmd.Parameters.AddWithValue("@MatKhau", matKhau);
                cmd.Parameters.AddWithValue("@MaVaiTro", 5);
                cmd.Parameters.AddWithValue("@MaCauHoi", maCauHoi);
                cmd.Parameters.AddWithValue("@NdTraLoi", ndTraLoi);
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region "Reset mật khẩu"
        public int ResetPassword(String tenThanhVien, int maCauHoi, string ndTraLoi)
        {
            var conn = new SqlConnection(SConn);
            conn.Open();
            var cmd = conn.CreateCommand();
            cmd.CommandText = "EXECUTE @count = ResetPassword @TenThanhVien, " + "@MaCauHoi, " + "@NdTraLoi";
            try
            {
                cmd.Parameters.AddWithValue("@TenThanhVien", tenThanhVien);
                cmd.Parameters.AddWithValue("@MaCauHoi", maCauHoi);
                cmd.Parameters.AddWithValue("@NdTraLoi", ndTraLoi);
                cmd.Parameters.Add("@count", SqlDbType.Int);
                cmd.Parameters["@count"].Direction = ParameterDirection.Output;
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine("reader[\"TenThanhVien\"] = " + reader["TenThanhVien"]);
                    Console.WriteLine("reader[\"MaCauHoi\"] = " + reader["MaCauHoi"]);
                    Console.WriteLine("reader[\"NdTraLoi\"] = " + reader["NdTraLoi"]);
                }
                reader.Close();
                return (int) cmd.Parameters["@count"].Value;
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region "Đổi mật khẩu"
        public int ChangePass(string tenThanhVien, string oldPass, string newPass)
        {
            var conn = new SqlConnection(SConn);
            conn.Open();
            var cmd = conn.CreateCommand();
            cmd.CommandText = "EXECUTE @result = ChangePassword @TenThanhVien, " + "@OldPassword, " + "@NewPassword";
            try
            {
                cmd.Parameters.AddWithValue("@TenThanhVien", tenThanhVien);
                cmd.Parameters.AddWithValue("@OldPassword", oldPass);
                cmd.Parameters.AddWithValue("@NewPassword", newPass);
                cmd.Parameters.Add("@result", SqlDbType.Int);
                cmd.Parameters["@result"].Direction = ParameterDirection.Output;
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine("reader[\"TenThanhVien\"] = " + reader["TenThanhVien"]);
                    Console.WriteLine("reader[\"MatKhau\"] = " + reader["MatKhau"]);
                }
                reader.Close();
                return (int)cmd.Parameters["@result"].Value;
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region "Lấy danh sách thành viên"
        public DataSet Binding()
        {
            var ds = new DataSet();
            var conn = new SqlConnection(SConn);
            var da = new SqlDataAdapter("GetAllMembers", conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.Fill(ds, "LoadData");
            return ds;
        }
        #endregion

        #region "Cấp quyền"
        public int UpdateRole(String tenThanhVien, String tenVaiTro)
        {
            var conn = new SqlConnection(SConn);
            conn.Open();
            var cmd = new SqlCommand("UpdateMember", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                cmd.Parameters.AddWithValue("@TenThanhVien", tenThanhVien);
                cmd.Parameters.AddWithValue("@TenVaiTro", tenVaiTro);
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region "Tìm kiếm"
        public DataSet Search(string tenThanhVien)
        {
            var ds = new DataSet();
            var conn = new SqlConnection(SConn);
            var da = new SqlDataAdapter("SearchMember", conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("@TenThanhVien", tenThanhVien);
            da.Fill(ds, "Search");
            return ds;
        }
        #endregion

        #region "Xóa thành viên"
        public int Delete(String tenThanhVien)
        {
            var conn = new SqlConnection(SConn);
            conn.Open();
            var cmd = new SqlCommand("DeleteMember", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                cmd.Parameters.AddWithValue("@TenThanhVien", tenThanhVien);
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                cmd.Dispose();
            }
        }
        #endregion
    }
}
